package lock.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author 徒有琴
 */
public class Buyer {
    Connection connection = DriverManager.getConnection("jdbc:mysql://www.hf1901.com:3306/1901", "root", "root");

    public Buyer() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        connection.setAutoCommit(false);
    }

    public int[] getAmount() throws Exception {
        String sql = "SELECT AMOUNT,ver FROM goods WHERE ID=1";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        int[] res = new int[2];
        if (resultSet.next()) {
            res[0] = resultSet.getInt("AMOUNT");
            res[1] = resultSet.getInt("ver");
        }
        resultSet.close();
        statement.close();
        return res;
    }

    public int updateAmount(int num, int ver) throws Exception {
        String sql = "UPDATE goods SET ver=ver+1, AMOUNT=AMOUNT-" + num + " WHERE ID=1 and ver=" + ver;
        Statement statement = connection.createStatement();
        int res = statement.executeUpdate(sql);
        connection.commit();
        statement.close();
        connection.close();
        return res;
    }

    public void release() throws Exception {
        connection.commit();
        connection.close();
    }
}
